const db = require("./db");
// jwt
const jwt = require("jsonwebtoken");
// 密钥
const secret = "liyu";

// 注册用户
// 参数
// user_id int AI PK
// username varchar(255)
// password varchar(255)
// full_name varchar(255)
// phone_number varchar(20)
// email varchar(255)
const registerUser = (userObj) => {
  const { username, password, full_name, phone_number, email } = userObj;
  return new Promise((resolve, reject) => {
    const sql = `insert into user (username,password,full_name,phone_number,email) values ('${username}','${password}','${full_name}','${phone_number}','${email}')`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

// 登录 参数 username password
const loginUser = (userObj) => {
  const { username, password } = userObj;
  return new Promise((resolve, reject) => {
    const tokenSte = jwt.sign({ username }, secret, { expiresIn: "12h" });
    const sql = `select user_id, username, full_name, phone_number, email, role from user where username='${username}' and password='${password}'`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        if (result.length > 0) {
          // 更新token
          const token = tokenSte;
          db.query(
            `update user set token='${token}' where username='${username}'`,
            (err, updateResult) => {
              if (err) {
                reject(err);
              } else {
                // 将token添加到结果中
                result[0].token = token;
                resolve(result);
              }
            }
          );
        } else {
          resolve(result);
        }
      }
    });
  });
};

// 修改用户信息 参数 user_id
const updateUser = (userObj) => {
  const { user_id, username, password, full_name, phone_number, email } =
    userObj;
  return new Promise((resolve, reject) => {
    const sql = `update user set username='${username}',password='${password}',full_name='${full_name}',phone_number='${phone_number}',email='${email}' where user_id=${user_id}`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        if (result.affectedRows > 0) {
          resolve("修改成功");
        } else {
          reject("修改失败");
        }
      }
    });
  });
};

// 菜品表
// 参数 id
const getDish = (id) => {
  const sql = `SELECT * FROM dish WHERE restaurant_id=${id} `;
  return new Promise((resolve, reject) => {
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        // 返回菜品列表
        resolve(result);
      }
    });
  });
};

// 餐厅表
// 参数 无
const getRestaurant = () => {
  return new Promise((resolve, reject) => {
    const sql = `select * from restaurant`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

// 创建购物车
const createCart = (userId, username, phoneNumber) => {
  return new Promise((resolve, reject) => {
    const sql = `INSERT INTO carts (user_id, username, phone_number) VALUES (?, ?, ?)`;
    db.query(sql, [userId, username, phoneNumber], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result.insertId);
      }
    });
  });
};

// 添加商品到购物车
const addToCart = (user_id, dish_id, dish_name, price, image_url, count) => {
  return new Promise((resolve, reject) => {
    const sql = `INSERT INTO cart_items (user_id, dish_id, dish_name, price, image_url, count) VALUES (?, ?, ?, ?, ?, ?)`;
    db.query(
      sql,
      [user_id, dish_id, dish_name, price, image_url, count],
      (err, result) => {
        if (err) {
          reject(err);
        } else {
          resolve("添加成功");
        }
      }
    );
  });
};

// 获取购物车和购物车项
const getCart = (userId) => {
  const cartPromise = new Promise((resolve, reject) => {
    const sql = `SELECT * FROM carts WHERE user_id = ?`;
    db.query(sql, [userId], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });

  const cartItemsPromise = new Promise((resolve, reject) => {
    const sql = `SELECT * FROM cart_items WHERE user_id = ?`;
    db.query(sql, [userId], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
  return Promise.all([cartPromise, cartItemsPromise]);
};

// 修改购物车商品数量
const updateCart = (id, count) => {
  return new Promise((resolve, reject) => {
    const sql = `UPDATE cart_items SET count = ? WHERE id = ?`;
    db.query(sql, [count, id], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve("修改成功");
      }
    });
  });
};

// 从购物车中删除商品
const deleteFromCart = (id) => {
  return new Promise((resolve, reject) => {
    const sql = `DELETE FROM cart_items WHERE id = ?`;
    db.query(sql, [id], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve("删除成功");
      }
    });
  });
};

// 订单
const createOrder = (
  userId,
  orderDate,
  totalPrice,
  orderStatus,
  username,
  address,
  contact,
  beizhu,
  couponDiscount = 0,
  originalPrice = null,
  usedCoupons = null
) => {
  return new Promise((resolve, reject) => {
    const sql = `
      INSERT INTO orders (
        UserID, OrderDate, TotalPrice, OrderStatus, address, contact, username, beizhu,
        coupon_discount, original_price, used_coupons
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;

    const params = [
      userId,
      orderDate,
      totalPrice,
      orderStatus,
      address,
      contact,
      username,
      beizhu,
      couponDiscount,
      originalPrice || (totalPrice + couponDiscount),
      usedCoupons ? JSON.stringify(usedCoupons) : null
    ];

    db.query(sql, params, function (err, result, fields) {
      if (err) {
        reject(err);
      } else {
        // 修改购物车状态为0 CartStatus
        const sql2 = `UPDATE cart_items SET CartStatus = 0 WHERE user_id = ?`;
        db.query(sql2, [userId], (err, updateResult) => {
          if (err) {
            reject(err);
          } else {
            resolve(result.insertId);
          }
        });
      }
    });
  });
};

const createOrderDetail = (
  orderId,
  dishId,
  dishName,
  price,
  imageURL,
  count,
  user_id
) => {
  return new Promise((resolve, reject) => {
    const sql = `INSERT INTO orderdetails (OrderID, DishID, dish_name, price, image_url, Quantity, user_id) VALUES (?, ?, ?, ?, ?, ?, ?)`;
    db.query(
      sql,
      [orderId, dishId, dishName, price, imageURL, count, user_id],
      (err, result) => {
        if (err) {
          reject(err);
        } else {
          resolve("添加成功");
        }
      }
    );
  });
};

// 获取订单 的订单详情和订单项
const getOrders = (userId) => {
  const orderPromise = new Promise((resolve, reject) => {
    const sql = `SELECT * FROM orders WHERE UserID = ?`;
    db.query(sql, [userId], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });

  const orderDetailsPromise = new Promise((resolve, reject) => {
    const sql = `SELECT * FROM orderdetails WHERE user_id = ?`;
    db.query(sql, [userId], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
  return Promise.all([orderPromise, orderDetailsPromise]);
};

// 取消订单 id zhuangtai
const cancelOrder = (id, orderStatus) => {
  return new Promise((resolve, reject) => {
    const sql = `update orders set OrderStatus='${orderStatus}' where OrderID=${id}`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve("取消成功");
      }
    });
  });
};

// 获取所有订单
const getAllOrders = () => {
  return new Promise((resolve, reject) => {
    const sql = `SELECT * FROM orders`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

// 增加餐厅
// 参数 restaurant_id int AI PK
// restaurant_name varchar(255)
// address varchar(255)
// contact_number varchar(20)
// image_url varchar(255)

const addRestaurant = (restaurantObj) => {
  const { restaurant_name, address, contact_number, image_url } = restaurantObj;
  return new Promise((resolve, reject) => {
    const sql = `insert into restaurant (restaurant_name,address,contact_number,image_url) values ('${restaurant_name}','${address}','${contact_number}','${image_url}')`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};
// 删除餐厅
// 参数 restaurant_id
const deleteRestaurant = (id) => {
  return new Promise((resolve, reject) => {
    const sql = `delete from restaurant where restaurant_id=${id}`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        if (result.affectedRows > 0) {
          resolve("删除成功");
        } else {
          reject("删除失败");
        }
      }
    });
  });
};

// 增加菜品
// 参数    dish_name: "",
//  price: "",
//  image_url: "",
//  description: "",
//  restaurant_id:
const addDish = (dishObj) => {
  const { dish_name, price, image_url, description, restaurant_id } = dishObj;
  return new Promise((resolve, reject) => {
    const sql = `insert into dish (dish_name,price,image_url,description,restaurant_id) values ('${dish_name}','${price}','${image_url}','${description}','${restaurant_id}')`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

// 删除菜品
// 参数 id
// 返回 删除成功或失败
const deleteDish = (id) => {
  return new Promise((resolve, reject) => {
    const sql = `delete from dish where dish_id=${id}`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        if (result.affectedRows > 0) {
          resolve("删除成功");
        } else {
          reject("删除失败");
        }
      }
    });
  });
};

// onSale 菜品上下架
const onSale1 = (id, onSale) => {
  return new Promise((resolve, reject) => {
    const sql = `update dish set on_sale=${onSale} where dish_id=${id}`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        if (result.affectedRows > 0) {
          resolve("修改成功");
        } else {
          reject("修改失败");
        }
      }
    });
  });
};

// 获取用户信息 根据id
// 参数 id
// 返回 用户信息
const getUserInfo = (id) => {
  return new Promise((resolve, reject) => {
    const sql = `select * from user where user_id=${id}`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

// /home/dishDetail/:id
// 获取菜品详情
// 参数 id
const getDishDetail = (id) => {
  return new Promise((resolve, reject) => {
    const sql = `select * from dish where dish_id=${id}`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

// 修改用户信息
const updateUserInfo = (
  user_id,
  username,
  password,
  full_name,
  phone_number,
  email,
  avatar = null
) => {
  return new Promise((resolve, reject) => {
    // 构建动态SQL，只更新提供的字段
    let updateFields = [];
    let params = [];

    if (username !== undefined && username !== null) {
      updateFields.push('username = ?');
      params.push(username);
    }
    if (password !== undefined && password !== null && password !== '') {
      updateFields.push('password = ?');
      params.push(password);
    }
    if (full_name !== undefined && full_name !== null) {
      updateFields.push('full_name = ?');
      params.push(full_name);
    }
    if (phone_number !== undefined && phone_number !== null) {
      updateFields.push('phone_number = ?');
      params.push(phone_number);
    }
    if (email !== undefined && email !== null) {
      updateFields.push('email = ?');
      params.push(email);
    }
    if (avatar !== undefined && avatar !== null) {
      updateFields.push('avatar = ?');
      params.push(avatar);
    }

    if (updateFields.length === 0) {
      reject("没有要更新的字段");
      return;
    }

    const sql = `UPDATE user SET ${updateFields.join(', ')} WHERE user_id = ?`;
    params.push(user_id);

    db.query(sql, params, (err, result) => {
      if (err) {
        reject(err);
      } else {
        if (result.affectedRows > 0) {
          resolve("修改成功");
        } else {
          reject("修改失败");
        }
      }
    });
  });
};
// 用户注销
// 参数 id
// 返回 删除成功或失败
const deleteUser = (id) => {
  return new Promise((resolve, reject) => {
    const sql = `delete from user where user_id=${id}`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        if (result.affectedRows > 0) {
          resolve("删除成功");
        } else {
          reject("删除失败");
        }
      }
    });
  });
};

// 管理员登录
// 获取所有普通用户信息
const getAllUsers = () => {
  return new Promise((resolve, reject) => {
    const sql = `select * from user`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};
// 管理员 删除用户
// 参数 id
// 返回 删除成功或失败
const adminDeleteUser = (id) => {
  return new Promise((resolve, reject) => {
    const sql = `delete from user where user_id=${id}`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        if (result.affectedRows > 0) {
          resolve("删除成功");
        } else {
          reject("删除失败");
        }
      }
    });
  });
};

// 餐厅详情
// 参数 id
const getRestaurantDetail = (id) => {
  return new Promise((resolve, reject) => {
    const sql = `select * from restaurant where restaurant_id=${id}`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

// 获取 删除了的历史订单
const getHistoryOrders = (user_id) => {
  return new Promise((resolve, reject) => {
    const sql1 = `SELECT * FROM orders WHERE UserID = ${user_id} AND OrderStatus = "删除订单"`;
    const sql2 = `SELECT * FROM orderdetails WHERE OrderID IN (SELECT OrderID FROM orders WHERE UserID = ${user_id} AND OrderStatus = "删除订单")`;

    // 使用 Promise.all() 同时执行两个查询
    Promise.all([
      new Promise((resolve, reject) => {
        db.query(sql1, (err, result) => {
          if (err) {
            reject(err);
          } else {
            resolve(result);
          }
        });
      }),
      new Promise((resolve, reject) => {
        db.query(sql2, (err, result) => {
          if (err) {
            reject(err);
          } else {
            resolve(result);
          }
        });
      }),
    ])
      .then(([orders, orderDetails]) => {
        // 将两个查询结果合并并返回
        resolve({ orders, orderDetails });
      })
      .catch(reject);
  });
};

// 餐厅上架下架状态
// 参数 id, status
const updateRestaurantStatus = (id, newStatus) => {
  return new Promise((resolve, reject) => {
    const sql = `update restaurant set status=${newStatus} where restaurant_id=${id}`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        if (result.affectedRows > 0) {
          resolve("修改成功");
        } else {
          reject("修改失败");
        }
      }
    });
  });
};
// 充值
const addRecharge = (date, amount, method, user_id) => {
  return new Promise((resolve, reject) => {
    const sql = `insert into recharge (recharge_date,amount,payment_method,user_id) values ('${date}',${amount},'${method}',${user_id})`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};
// 获取充值记录
const getRecharge = (user_id) => {
  return new Promise((resolve, reject) => {
    const sql = `select * from recharge where user_id=${user_id}`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};
// 计算使用的余额:消费金额表expenditure所有消费的amount字段
// 参数 user_id
// 返回 余额
const calculateBalance = (user_id) => {
  return new Promise((resolve, reject) => {
    const sql = `SELECT SUM(amount) AS totalAmount FROM expenditure WHERE user_id = ?`;
    db.query(sql, [user_id], (err, result) => {
      if (err) {
        reject(err);
      } else {
        const totalAmount = result[0].totalAmount || 0;
        resolve(totalAmount);
      }
    });
  });
};

// 计算剩余金额=充值金额表recharge所有充值的amount字段-消费金额表expenditure所有消费的amount字段
const calculateTotalAmount = (user_id) => {
  return new Promise((resolve, reject) => {
    // 查询充值总金额
    const queryRecharge =
      "SELECT SUM(amount) AS totalRecharge FROM recharge WHERE user_id = ?";
    db.query(queryRecharge, [user_id], (error, results) => {
      if (error) {
        reject(error);
        return;
      }
      const totalRecharge = results[0].totalRecharge || 0;
      // 查询消费总金额
      const queryExpenditure =
        "SELECT SUM(amount) AS totalExpenditure FROM expenditure WHERE user_id = ?";
      db.query(queryExpenditure, [user_id], (error, results) => {
        if (error) {
          reject(error);
          return;
        }

        const totalExpenditure = results[0].totalExpenditure || 0;

        // 计算剩余金额
        const remainingAmount = totalRecharge - totalExpenditure;
        resolve(remainingAmount);
      });
    });
  });
};

// 充值金额表recharge所有充值的amount字段
const getRechargeAmount = (user_id) => {
  return new Promise((resolve, reject) => {
    const sql = `SELECT SUM(amount) AS totalRecharge FROM recharge WHERE user_id = ?`;
    db.query(sql, [user_id], (err, result) => {
      if (err) {
        reject(err);
      } else {
        const totalRecharge = result[0].totalRecharge || 0;
        resolve(totalRecharge);
      }
    });
  });
};

// 添加消费记录
const addExpenditure = (date, amount, user_id) => {
  return new Promise((resolve, reject) => {
    const sql = `insert into expenditure (date,amount,user_id) values ('${date}',${amount},${user_id})`;
    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

// // 菜单评论{
const addComment = (user_id, dish_id, rating, comment, formattedDateTime) => {
  return new Promise((resolve, reject) => {
    const sql = `insert into menu_reviews (user_id,dish_id,rating,comment,review_date) values (${user_id},${dish_id},${rating},'${comment}','${formattedDateTime}')`;

    db.query(sql, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

// 充值申请相关函数
const createRechargeRequest = (user_id, amount, payment_method) => {
  return new Promise((resolve, reject) => {
    const sql = `INSERT INTO recharge_requests (user_id, amount, payment_method) VALUES (?, ?, ?)`;
    db.query(sql, [user_id, amount, payment_method], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

const getRechargeRequests = (status = null) => {
  return new Promise((resolve, reject) => {
    let sql = `
      SELECT rr.*, u.username, u.full_name
      FROM recharge_requests rr
      LEFT JOIN user u ON rr.user_id = u.user_id
    `;
    let params = [];

    if (status) {
      sql += ` WHERE rr.status = ?`;
      params.push(status);
    }

    sql += ` ORDER BY rr.request_time DESC`;

    db.query(sql, params, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

const getUserRechargeRequests = (user_id) => {
  return new Promise((resolve, reject) => {
    const sql = `
      SELECT rr.*, a.username as admin_username
      FROM recharge_requests rr
      LEFT JOIN user a ON rr.admin_id = a.user_id
      WHERE rr.user_id = ?
      ORDER BY rr.request_time DESC
    `;
    db.query(sql, [user_id], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

const processRechargeRequest = (request_id, admin_id, status, admin_comment = null) => {
  return new Promise((resolve, reject) => {
    const sql = `
      UPDATE recharge_requests
      SET status = ?, admin_id = ?, admin_comment = ?, processed_time = CURRENT_TIMESTAMP
      WHERE id = ?
    `;
    db.query(sql, [status, admin_id, admin_comment, request_id], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

// 优惠券相关函数
const getAvailableCoupons = (user_id = null) => {
  return new Promise((resolve, reject) => {
    let sql = `
      SELECT * FROM coupons
      WHERE status = 'active'
      AND start_date <= NOW()
      AND end_date >= NOW()
      AND (usage_limit IS NULL OR used_count < usage_limit)
      ORDER BY value DESC
    `;

    db.query(sql, [], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

const validateCoupon = (code, amount, user_id = null) => {
  return new Promise((resolve, reject) => {
    const sql = `
      SELECT * FROM coupons
      WHERE code = ?
      AND status = 'active'
      AND start_date <= NOW()
      AND end_date >= NOW()
      AND min_amount <= ?
      AND (usage_limit IS NULL OR used_count < usage_limit)
    `;

    db.query(sql, [code, amount], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

const calculateDiscount = (coupon, amount) => {
  if (coupon.type === 'percentage') {
    let discount = (amount * coupon.value) / 100;
    if (coupon.max_discount && discount > coupon.max_discount) {
      discount = coupon.max_discount;
    }
    return discount;
  } else if (coupon.type === 'fixed') {
    return Math.min(coupon.value, amount);
  }
  return 0;
};

const applyCoupons = (coupons, amount) => {
  let totalDiscount = 0;
  let validCoupons = [];

  for (const coupon of coupons) {
    if (amount >= coupon.min_amount) {
      const discount = calculateDiscount(coupon, amount - totalDiscount);
      if (discount > 0) {
        totalDiscount += discount;
        validCoupons.push({
          ...coupon,
          discount: discount
        });
      }
    }
  }

  return {
    totalDiscount: Math.min(totalDiscount, amount),
    validCoupons: validCoupons,
    finalAmount: Math.max(0, amount - totalDiscount)
  };
};

const useCoupon = (coupon_id, user_id, order_id) => {
  return new Promise((resolve, reject) => {
    db.beginTransaction((err) => {
      if (err) {
        reject(err);
        return;
      }

      // 更新优惠券使用次数
      const updateCouponSql = `UPDATE coupons SET used_count = used_count + 1 WHERE id = ?`;
      db.query(updateCouponSql, [coupon_id], (err, result) => {
        if (err) {
          db.rollback();
          reject(err);
          return;
        }

        // 记录用户使用优惠券
        const insertUserCouponSql = `
          INSERT INTO user_coupons (user_id, coupon_id, status, used_at, order_id)
          VALUES (?, ?, 'used', NOW(), ?)
        `;
        db.query(insertUserCouponSql, [user_id, coupon_id, order_id], (err, result) => {
          if (err) {
            db.rollback();
            reject(err);
            return;
          }

          db.commit((err) => {
            if (err) {
              db.rollback();
              reject(err);
              return;
            }
            resolve(result);
          });
        });
      });
    });
  });
};

module.exports = {
  registerUser,
  loginUser,
  getDish,
  updateUser,
  getRestaurant,
  createCart,
  addToCart,
  getCart,
  deleteFromCart,
  updateCart,
  createOrder,
  createOrderDetail,
  getOrders,
  addRestaurant,
  deleteRestaurant,
  addDish,
  deleteDish,
  getUserInfo,
  updateUserInfo,
  deleteUser,
  getAllUsers,
  adminDeleteUser,
  getRestaurantDetail,
  onSale1,
  getDishDetail,
  cancelOrder,
  getAllOrders,
  getHistoryOrders,
  updateRestaurantStatus,
  addRecharge,
  getRecharge,
  calculateBalance,
  calculateTotalAmount,
  getRechargeAmount,
  addExpenditure,
  addComment,
  // 充值申请相关
  createRechargeRequest,
  getRechargeRequests,
  getUserRechargeRequests,
  processRechargeRequest,
  // 优惠券相关
  getAvailableCoupons,
  validateCoupon,
  calculateDiscount,
  applyCoupons,
  useCoupon,
};
